<?php
require_once($_SESSION["root_path"]."/general_queries.inc");
$subjects = DataBase::select("subject",null,array("selection_campaign_id"=>$_SESSION["campaign_id"]));
$programs = query_programs()->execute();
$_SESSION['program'] = $programs;
$_SESSION['subjects'] = $subjects;


echo "<b>Status:</b><ul style='margin:0px'>";

// attendance
$result = DataBase::query("SELECT * FROM applicant LEFT JOIN applicant_more_info ON applicant_more_info.applicant_id=applicant.applicant_id WHERE selection_campaign_id=".$_SESSION["campaign_id"]." AND (applicant_more_info_attendee_exam_status=0 OR applicant_more_info_attendee_exam_status IS NULL)");
if ($result == false)
	$nbNotYet = 0;
else
	$nbNotYet = count($result);

$_SESSION["session_nbNotYet"] = $nbNotYet;

if ($nbNotYet==0) {
	echo "<li><span style='color:green'>All applicants attendance have been specified.</span></li>";
} else {
	echo "<li><span style='color:red'>".$nbNotYet." applicant(s) still have the status 'Not Yet' on their written exam attendance.</span></li>";
}

// marks in the system
$query = "SELECT COUNT(*) FROM applicant";
$query .= " LEFT JOIN applicant_more_info ON applicant_more_info.applicant_id=applicant.applicant_id";
for ($i = 0; $i < count($subjects); $i++)
	$query .= " LEFT JOIN applicant_score_subject_exam AS score_".$i." ON (score_".$i.".applicant_id=applicant.applicant_id AND score_".$i.".subject_id=".$subjects[$i]["subject_id"].")";
$query .= " WHERE selection_campaign_id=".$_SESSION["campaign_id"]." AND applicant_more_info_attendee_exam_status=1";
for ($i = 0; $i < count($subjects); $i++)
	$query .= " AND score_".$i.".score_subject_exam IS NOT NULL";

//echo "<br />Sophea: ". $query."<br />";

$result = DataBase::query($query);

$nbFullyOk = $result[0][0];

$_SESSION['session_nbFullyOk'] = $nbFullyOk;

$query = "SELECT COUNT(*) FROM applicant";
$query .= " LEFT JOIN applicant_more_info ON applicant_more_info.applicant_id=applicant.applicant_id";
for ($i = 0; $i < count($subjects); $i++)
	$query .= " LEFT JOIN applicant_score_subject_exam AS score_".$i." ON (score_".$i.".applicant_id=applicant.applicant_id AND score_".$i.".subject_id=".$subjects[$i]["subject_id"].")";
$query .= " WHERE selection_campaign_id=".$_SESSION["campaign_id"]." AND applicant_more_info_attendee_exam_status=1";
$query .= " AND (";
for ($i = 0; $i < count($subjects); $i++) {
	if ($i > 0) $query .= " OR ";
	$query .= "score_".$i.".score_subject_exam IS NULL";
}
$query .= ")";
$result = DataBase::query($query);
$nbNotFullyOk = $result[0][0];

$_SESSION['session_nbNotFullyOk'] = $nbNotFullyOk;

if ($nbNotFullyOk > 0){
        $nbNotFullyOkView = $nbNotFullyOk - 1;
	echo "<li><span style='color:red'>".$nbNotFullyOkView." applicant(s) who attend the exam do not have marks or only part of the marks in the system</span> (".$nbFullyOk." applicant(s) are OK)</li>";
}else{
	echo "<li><span style='color:green'>The ".$nbFullyOk." applicant(s) who attend the exam have all the marks in the system</span></li>";
}

// eligibility set
$q = "SELECT COUNT(*) FROM applicant";
foreach ($programs as $p) {
	$pid = $p["program_id"];
	$q .= " LEFT JOIN applicant_eligibility AS e".$pid." ON e".$pid.".applicant_id=applicant.applicant_id";
}
$q .= " WHERE applicant.excluded=0 AND applicant.selection_campaign_id=".$_SESSION["campaign_id"];
foreach ($programs as $p) {
	$pid = $p["program_id"];
	$q .= " AND e".$pid.".eligible IS NULL";
}
$res = DataBase::query($q);
if ($res == false || count($res)==0)
	$nbEligibilityNotSet = 0;
else
	$nbEligibilityNotSet = $res[0][0];

$_SESSION['nbEligibilityNotSet'] = $nbEligibilityNotSet;
if ($nbEligibilityNotSet == 0)
	echo "<li><span style='color:green'>All applicants have their eligibility set for all programs</span></li>";
else
	echo "<li><span style='color:red'>".$nbEligibilityNotSet." applicant(s) do not have their eligibility set for all programs</span></li>";

// eligibility for each program
$s = "";
$previous_programs = array();
foreach ($programs as $p) {
	$q = "SELECT COUNT(*) FROM applicant";
	$q .= " LEFT JOIN applicant_eligibility AS current_program ON (current_program.applicant_id=applicant.applicant_id AND current_program.program_id=".$p["program_id"].")";
	for ($i = 0; $i < count($previous_programs); $i++)
		$q .= " LEFT JOIN applicant_eligibility AS previous_program_".$i." ON (previous_program_".$i.".applicant_id=applicant.applicant_id AND previous_program_".$i.".program_id=".$previous_programs[$i]["program_id"].")";
	$q .= " WHERE selection_campaign_id=".$_SESSION["campaign_id"];
	$q .= " AND current_program.eligible=1";
	for ($i = 0; $i < count($previous_programs); $i++)
		$q .= " AND previous_program_".$i.".eligible=0";
	$res = DataBase::query($q);
	if ($s<>"") $s.=", ";
	$s .= $res[0][0]." eligibles for ".$p["program_name"];
	array_push($previous_programs, $p);
}

$_SESSION['var_s'] = $s;
echo "<li>Eligibility status: ".$s."</li>";

echo "</ul>";
?>